package Application.Dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import DomainData.MainCourse;
import Reservation.MysqlConnection;
public class MainCourseDAO {
	static Connection connection = null;
	static ResultSet rs = null;
	public static void insertMainCourse(MainCourse mainCourse){
		try{
			// before Insert user data, get connection with database
			connection = MysqlConnection.getConnection();
			//try to write Insert query for preparing
			if(connection != null){
				java.sql.PreparedStatement stat = connection.prepareStatement("INSERT INTO maincourse (name, description, price) "+
			"VALUES(?,?,?)");
				stat.setString(1, mainCourse.getCourseName());
				stat.setString(2, mainCourse.getCourseDescription());
				stat.setDouble(3, mainCourse.getCoursePrice());
				stat.executeUpdate();

				Statement statement = connection.createStatement();
				ResultSet rs =statement.executeQuery("select LAST_INSERT_ID()");
				int mainCourseID=0;
				if(rs.next()){
					mainCourseID = rs.getInt("last_insert_id()");
				}
				
				mainCourse.setID(mainCourseID);
				
				System.out.println("You have successfully registered.");
				
			}
		}
		catch(Exception e){
			e.printStackTrace();
			System.out.println("Connection is wrong</br>");
			System.out.println(e.toString());
		}
		finally {
			try {
				connection.close();
				} catch(Exception ee) {};
			}
	}
	public static ArrayList<MainCourse> getMainCourseList(){
		ArrayList<MainCourse> mlist = new ArrayList<MainCourse>();
		Statement statement = null;
		String sql = "select * from maincourse";
		try {
			connection = MysqlConnection.getConnection();
			if (connection==null){
				System.out.println("********Connection is null");
			}

			statement = connection.createStatement();
			rs = statement.executeQuery(sql);
			while(rs.next()){
				int id = rs.getInt(1);
				String name = rs.getString(2);
				String description = rs.getString(3);
				double price = rs.getDouble(4);
				MainCourse mc = new MainCourse(id,name,description,price);
				mlist.add(mc);
			}
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return mlist;
	}
	
	public static List<MainCourse> getAllMainCourses(){
		List<MainCourse> list = new ArrayList<MainCourse>();
		try {
			connection = MysqlConnection.getConnection();
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Statement statement = null;
		String sql = "SELECT * FROM `fkou`.maincourse ORDER BY mainCourseID asc";
		try {
			statement = connection.createStatement();
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				MainCourse mc = getMainCourseFromRs(rs);
				list.add(mc);
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} finally {

			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return list;
		
	}
	private static MainCourse getMainCourseFromRs(ResultSet rs) {
		MainCourse mc = null;
		try {
			mc = new MainCourse(rs.getInt("mainCourseID"),
					rs.getString("name"), rs.getString("description"),
					rs.getDouble("price"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return mc;
	}
}
